package Model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Database.MySQLController;

/**
 * This class StaffAppointment serves as an entity model 
 * with its attributes and their accessor and mutator methods.
 * @category Entity Model
 * @author GoyDingXian, Previous batches for FYPJ QMS
 * @version 3.1
 */
public class StaffAppointment
{

	private String loginID;
	private String appointmentID;
	private String status;
	private String name;
	private String effectiveDate;
	private String endDate;
	private String approvedBy;
	private String dateUpdated;
	private String apptGrpID;
	private String md5;
	/// @attribute year => Gregorian Calendar Year
	private String year;
	private String saclass;
	private String diploma;
	private String createdBy;
	///	@attribute yearNo sets the year in which STL is i/c -> stage 1/2/3
	private String yearNo;
	private String responsibilities;
	
	public StaffAppointment() {
		super();
		this.yearNo = "";
		this.name = "";
		this.diploma = "";
	}
	
	public String getResponsibilities() {
		return responsibilities;
	}

	public void setResponsibilities(String responsibilities) {
		this.responsibilities = responsibilities;
	}

	public String getYearNo() {
		return yearNo;
	}

	public void setYearNo(String yearNo) {
		this.yearNo = yearNo;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
	public String getYear() {
		return year;
	}

	public void setYear(String year) {
		this.year = year;
	}

	public String getSaclass() {
		return saclass;
	}

	public void setSaclass(String saclass) {
		this.saclass = saclass;
	}

	public String getDiploma() {
		return diploma;
	}

	public void setDiploma(String diploma) {
		this.diploma = diploma;
	}

	public String getLoginID()
	{
		return loginID;
	}

	public void setLoginID(String loginID)
	{
		this.loginID = loginID;
	}

	public String getAppointmentID()
	{
		return appointmentID;
	}

	public void setAppointmentID(String appointmentID)
	{
		this.appointmentID = appointmentID;
	}

	public String getStatus()
	{
		return status;
	}

	public void setStatus(String status)
	{
		this.status = status;
	}

	public String getEffectiveDate()
	{
		return effectiveDate;
	}

	public void setEffectiveDate(String effectiveDate)
	{
		this.effectiveDate = effectiveDate;
	}

	public String getEndDate()
	{
		return endDate;
	}

	public void setEndDate(String endDate)
	{
		this.endDate = endDate;
	}

	public String getApprovedBy()
	{
		return approvedBy;
	}

	public void setApprovedBy(String approvedBy)
	{
		this.approvedBy = approvedBy;
	}

	public String getDateUpdated()
	{
		return dateUpdated;
	}

	public void setDateUpdated(String dateUpdated)
	{
		this.dateUpdated = dateUpdated;
	}

	public String getApptGrpID()
	{
		return apptGrpID;
	}

	public void setApptGrpID(String apptGrpID)
	{
		this.apptGrpID = apptGrpID;
	}

	public String getMd5()
	{
		return md5;
	}

	public void setMd5(String md5)
	{
		this.md5 = md5;
	}

	public String getCreatedBy() {
		return createdBy;
	}

	public void setCreatedBy(String createdBy) {
		this.createdBy = createdBy;
	}

	/***
	 * 
	 * @param appointmentName
	 * @return
	 */
	public static ArrayList<StaffAppointment> getStaffsForAppointment(String appointmentName)
	{
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		ArrayList<StaffAppointment> staffAptList = new ArrayList<StaffAppointment>();
		String dbQuery = "";
		if(appointmentName.equalsIgnoreCase("Course Coordinator"))
		{
		dbQuery += "SELECT * FROM qmsdb.staffappointment "
				+ "INNER JOIN staff ON UA_LoginID = Staff_LoginID "
				+ "INNER JOIN appointment ON UA_AppointmentID = Appointment_ID "
				+ "WHERE Appointment_Name = '" + appointmentName + "' ORDER BY UA_Diploma, UA_Year ,UA_Class, UA_YearNo";
		}else
		{
			dbQuery = "SELECT * FROM qmsdb.staffappointment "
					+ "INNER JOIN staff ON UA_LoginID = Staff_LoginID "
					+ "INNER JOIN appointment ON UA_AppointmentID = Appointment_ID "
					+ "WHERE Appointment_Name = '" + appointmentName + "' ORDER BY UA_Year , UA_Diploma,UA_Class, UA_YearNo";
		}
		ResultSet rs = null;
		try{
			rs = mysql.readRequest(dbQuery);
			while(rs.next())
			{
				StaffAppointment sa = new StaffAppointment();
				sa.setAppointmentID(rs.getString("Appointment_ID"));
	 			sa.setApprovedBy(rs.getString("UA_ApprovedBy"));
				sa.setSaclass(rs.getString("UA_Class"));
				sa.setName(rs.getString("Staff_Name"));
				//	Sets e course
				sa.setDiploma(rs.getString("UA_Diploma"));
				sa.setYear(rs.getString("UA_Year"));
				sa.setYearNo(rs.getString("UA_YearNo"));
				sa.setResponsibilities(rs.getString("Appointment_Responsibilities"));
				staffAptList.add(sa);
			}
		}catch(SQLException sqlErr)
		{
			System.out.println(sqlErr.getLocalizedMessage());
		}finally
		{
			mysql.terminate();
		}
		return staffAptList;
	}
	
	/***
	 * 
	 * @param UA_MD5
	 * @return
	 */
	public boolean removeStaffappointment(String UA_MD5)
	{
		boolean success = false;
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "DELETE FROM staffappointment WHERE UA_MD5 ='" + UA_MD5 + "'";
		try{
			if(mysql.updateRequest(dbQuery) == 1)
			{
				success = true;
			}
		}catch(SQLException sqlErr)
		{
			System.out.println(sqlErr.getLocalizedMessage());
		}finally
		{
			mysql.terminate();
		}
		return success;
	}
	
	/**
	 * Allows bulk removal of committee.
	 * @param committeeIDList
	 * @return true if committee removed successfully, false if unsuccessful
	 */
	public boolean removeStaffappointment(String[] appointmentIDList)
	{
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "DELETE FROM staffappointment WHERE Appointment_ID IN (";
		System.out.println("appointmentIDList length is : " + appointmentIDList.length);
		for(int i = 0 ; i < appointmentIDList.length;i++)
		{
		
			if(appointmentIDList.length == 1)
			{
				dbQuery += "'" + appointmentIDList[i] + "');";
				break;
			}
			if(i == appointmentIDList.length - 1)
			{
				dbQuery += "'" + appointmentIDList[i] + "');";
			}else
			{
				dbQuery += "'" + appointmentIDList[i] + "',";
			}
			System.out.println(dbQuery);
		}
		try{
			if(mysql.updateRequest(dbQuery) > 0)
			{
				return true;
			}
		}catch(SQLException sqlErr)
		{
			sqlErr.printStackTrace();
		}finally
		{
			mysql.terminate();
		}
		return false;

	}
	public static String getAppointmentName(int appointmentID)
	{
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "SELECT Appointment_Name FROM appointment WHERE Appointment_ID = " + appointmentID;
		ResultSet rs = null;
		try{
			rs = mysql.readRequest(dbQuery);
			if(rs.next())
			{
				return rs.getString("Appointment_Name");
			}
		}catch(SQLException sqlEx)
		{
			sqlEx.printStackTrace();
		}finally{
			mysql.terminate();
		}
		return "";
	}
	
	@Override
	public String toString() {
		return String
				.format("StaffAppointment [loginID=%s, appointmentID=%s, status=%s, name=%s, effectiveDate=%s, endDate=%s, approvedBy=%s, dateUpdated=%s, apptGrpID=%s, md5=%s, year=%s, saclass=%s, diploma=%s, createdBy=%s, yearNo=%s, responsibilities=%s]",
						loginID, appointmentID, status, name, effectiveDate,
						endDate, approvedBy, dateUpdated, apptGrpID, md5, year,
						saclass, diploma, createdBy, yearNo, responsibilities);
	}

	/***
	 * 
	 * @param appointmentName
	 * @param year
	 * @return
	 */
	public static ArrayList<StaffAppointment> getStaffsForAppointmentByYear(
			String appointmentName,int year) throws NullPointerException {
		ArrayList<StaffAppointment> staffAptList = new ArrayList<StaffAppointment>();
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "SELECT * FROM qmsdb.staffappointment "
				+ "INNER JOIN staff ON UA_LoginID = Staff_LoginID "
				+ "INNER JOIN appointment ON UA_AppointmentID = Appointment_ID "
				+ "WHERE Appointment_Name = 'Personal Mentor' AND UA_Year = '" + year + "' "
				+ "ORDER BY UA_Year , UA_Diploma,UA_Class, UA_YearNo;";
		ResultSet rs = null;
		try{
			rs = mysql.readRequest(dbQuery);
			while(rs.next())
			{
				StaffAppointment staffApt = new StaffAppointment();
				staffApt.setAppointmentID(rs.getString("Appointment_ID"));
	 			staffApt.setApprovedBy(rs.getString("UA_ApprovedBy"));
				staffApt.setSaclass(rs.getString("UA_Class"));
				staffApt.setName(rs.getString("Staff_Name"));
				//	Sets e course
				staffApt.setDiploma(rs.getString("UA_Diploma"));
				staffApt.setYear(rs.getString("UA_Year"));
				staffApt.setYearNo(rs.getString("UA_YearNo"));
				staffApt.setResponsibilities(rs.getString("Appointment_Responsibilities"));
				staffAptList.add(staffApt);
			}
			return staffAptList;
		}catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		finally{
			mysql.terminate();
		}
		return null;
	}
	
	public static String getResponsibilitiesForAppointment(String appointmentName)
	{
		MySQLController mysql = new MySQLController();
		mysql.setUp();
		String dbQuery = "SELECT Appointment_Responsibilities FROM appointment WHERE Appointment_Name = '" + appointmentName + "';";
		System.out.println(dbQuery);
		ResultSet rs = null;
		try{
			rs = mysql.readRequest(dbQuery);
			if(rs.next())
			{
				return rs.getString("Appointment_Responsibilities");
			}
		}catch(SQLException sqlEx)
		{
			sqlEx.printStackTrace();
		}
		return "";
	}



}
